# -*- coding: utf8 -*-

from project.sqlite3_db.table import Table

class TableImageVector10( Table ):

    _table_name = "image_vector10"

    def create( self ):
        cur = self.getCursor()

        # Create table
        cur.execute(
            "CREATE TABLE IF NOT EXISTS image_vector10 ("+
            "id INTEGER PRIMARY KEY, "+
            "image_id INTEGER, "+
            "orientation INTEGER, "+
            "v0 INTEGER, "+
            "v1 INTEGER, "+
            "v2 INTEGER, "+
            "v3 INTEGER, "+
            "v4 INTEGER, "+
            "v5 INTEGER, "+
            "v6 INTEGER, "+
            "v7 INTEGER, "+
            "v8 INTEGER, "+
            "v9 INTEGER)"
        )
        cur.execute(
            "CREATE INDEX IF NOT EXISTS images ON image_vector10 (image_id)"
        )
        cur.execute(
            "CREATE INDEX IF NOT EXISTS vector ON image_vector10 (v0,v1,v2,v3,v4,v5,v6,v7,v8,v9)"
        )
        cur.close()
        return


    def insert_image_v10( self, image_id, orientation,
                          v0, v1, v2, v3, v4, v5, v6, v7, v8, v9 ):
        cur = self.getCursor()

        cur.execute(
            "INSERT INTO image_vector10("+
            "image_id, orientation, v0, v1, v2, v3, v4, v5, v6, v7, v8, v9"+
            ") VALUES ("+
            ":image_id, :orientation, :v0, :v1, :v2, :v3, :v4, :v5, :v6, :v7, :v8, :v9"+
            ")", {
                "image_id":image_id,
                "orientation":orientation,
                "v0":v0, "v1":v1, "v2":v2, "v3":v3, "v4":v4, "v5":v5, "v6":v6, "v7":v7, "v8":v8, "v9":v9
            }
        )
        self.getConnection().commit()
        v_id = cur.lastrowid
        cur.close()
        return v_id


    def get_image_vectors(self, image_id):
        cur = self.getCursor()
        cur.execute("SELECT * FROM image_vector10 WHERE image_id=:value ORDER BY orientation ASC",
                {"value": image_id}
        )
        items = cur.fetchall()
        cur.close()
        return items


    def get_similar_images_old(self, image_vector):
        cur = self.getCursor()
        proc = 0.05
        cur.execute("SELECT image_id, "+
                    "abs(v0-:v0)<=max(v0*:proc,:dv0) as c0, "
                    "abs(v1-:v1)<=max(v1*:proc,:dv1) as c1, "+
                    "abs(v2-:v2)<=max(v2*:proc,:dv2) as c2, "+
                    "abs(v3-:v3)<=max(v3*:proc,:dv3) as c3, "+
                    "abs(v4-:v4)<=max(v4*:proc,:dv4) as c4, "+
                    "abs(v5-:v5)<=max(v5*:proc,:dv5) as c5, "+
                    "abs(v6-:v6)<=max(v6*:proc,:dv6) as c6, "+
                    "abs(v7-:v7)<=max(v7*:proc,:dv7) as c7, "+
                    "abs(v8-:v8)<=max(v8*:proc,:dv8) as c8, "+
                    "abs(v9-:v9)<=max(v9*:proc,:dv9) as c9 "+
                    "FROM image_vector10 WHERE "+
                    "image_id != :id AND ( c0+c1+c2+c3+c4+c5+c6+c7+c8+c9 ) >= 9 " +
                    "ORDER BY orientation ASC",
                {
                "id": image_vector["image_id"],
                "v0": image_vector["v0"],
                "dv0": int(image_vector["v0"]*proc),
                "v1": image_vector["v1"],
                "dv1": int(image_vector["v1"]*proc),
                "v2": image_vector["v2"],
                "dv2": int(image_vector["v2"]*proc),
                "v3": image_vector["v3"],
                "dv3": int(image_vector["v3"]*proc),
                "v4": image_vector["v4"],
                "dv4": int(image_vector["v4"]*proc),
                "v5": image_vector["v5"],
                "dv5": int(image_vector["v5"]*proc),
                "v6": image_vector["v6"],
                "dv6": int(image_vector["v6"]*proc),
                "v7": image_vector["v7"],
                "dv7": int(image_vector["v7"]*proc),
                "v8": image_vector["v8"],
                "dv8": int(image_vector["v8"]*proc),
                "v9": image_vector["v9"],
                "dv9": int(image_vector["v9"]*proc),
                "proc":proc
            }
        )
        items = cur.fetchall()
        cur.close()
        return items

    def get_similar_images(self, image_vector):
        cur = self.getCursor()
        proc = 0.05

        compare = {
            "id": image_vector["image_id"],
            "dv01": int(image_vector["v0"]*(1-proc)),
            "dv02": int(image_vector["v0"]*(1+proc)),
            "dv11": int(image_vector["v1"]*(1-proc)),
            "dv12": int(image_vector["v1"]*(1+proc)),
            "dv21": int(image_vector["v2"]*(1-proc)),
            "dv22": int(image_vector["v2"]*(1+proc)),
            "dv31": int(image_vector["v3"]*(1-proc)),
            "dv32": int(image_vector["v3"]*(1+proc)),
            "dv41": int(image_vector["v4"]*(1-proc)),
            "dv42": int(image_vector["v4"]*(1+proc)),
            "dv51": int(image_vector["v5"]*(1-proc)),
            "dv52": int(image_vector["v5"]*(1+proc)),
            "dv61": int(image_vector["v6"]*(1-proc)),
            "dv62": int(image_vector["v6"]*(1+proc)),
            "dv71": int(image_vector["v7"]*(1-proc)),
            "dv72": int(image_vector["v7"]*(1+proc)),
            "dv81": int(image_vector["v8"]*(1-proc)),
            "dv82": int(image_vector["v8"]*(1+proc)),
            "dv91": int(image_vector["v9"]*(1-proc)),
            "dv92": int(image_vector["v9"]*(1+proc)),
        }

        fetched = []

        cur.execute(
            "SELECT image_id "+
            "FROM image_vector10 WHERE "+
            "image_id != :id AND (" +

            "( v0>=:dv01 AND v0<=:dv02 AND " +
            "v1>=:dv11 AND v1<=:dv12 AND " +
            "v2>=:dv21 AND v2<=:dv22 AND " +
            "v3>=:dv31 AND v3<=:dv32 AND " +
            "v4>=:dv41 AND v4<=:dv42 AND " +
            "v5>=:dv51 AND v5<=:dv52 AND " +
            "v6>=:dv61 AND v6<=:dv62 AND " +
            "v7>=:dv71 AND v7<=:dv72 AND " +
            "v8>=:dv81 AND v8<=:dv82 ) OR " +

            "( v0>=:dv01 AND v0<=:dv02 AND " +
            "v1>=:dv11 AND v1<=:dv12 AND " +
            "v2>=:dv21 AND v2<=:dv22 AND " +
            "v3>=:dv31 AND v3<=:dv32 AND " +
            "v4>=:dv41 AND v4<=:dv42 AND " +
            "v5>=:dv51 AND v5<=:dv52 AND " +
            "v6>=:dv61 AND v6<=:dv62 AND " +
            "v7>=:dv71 AND v7<=:dv72 AND " +
            "v9>=:dv91 AND v8<=:dv92 ) OR " +

            "( v0>=:dv01 AND v0<=:dv02 AND " +
            "v1>=:dv11 AND v1<=:dv12 AND " +
            "v2>=:dv21 AND v2<=:dv22 AND " +
            "v3>=:dv31 AND v3<=:dv32 AND " +
            "v4>=:dv41 AND v4<=:dv42 AND " +
            "v5>=:dv51 AND v5<=:dv52 AND " +
            "v6>=:dv61 AND v6<=:dv62 AND " +
            "v9>=:dv91 AND v9<=:dv92 AND " +
            "v8>=:dv81 AND v8<=:dv82 ) OR " +

            "( v0>=:dv01 AND v0<=:dv02 AND " +
            "v1>=:dv11 AND v1<=:dv12 AND " +
            "v2>=:dv21 AND v2<=:dv22 AND " +
            "v3>=:dv31 AND v3<=:dv32 AND " +
            "v4>=:dv41 AND v4<=:dv42 AND " +
            "v5>=:dv51 AND v5<=:dv52 AND " +
            "v9>=:dv91 AND v9<=:dv92 AND " +
            "v7>=:dv71 AND v7<=:dv72 AND " +
            "v8>=:dv81 AND v8<=:dv82 ) OR " +

            "( v0>=:dv01 AND v0<=:dv02 AND " +
            "v1>=:dv11 AND v1<=:dv12 AND " +
            "v2>=:dv21 AND v2<=:dv22 AND " +
            "v3>=:dv31 AND v3<=:dv32 AND " +
            "v4>=:dv41 AND v4<=:dv42 AND " +
            "v9>=:dv91 AND v9<=:dv92 AND " +
            "v6>=:dv61 AND v6<=:dv62 AND " +
            "v7>=:dv71 AND v7<=:dv72 AND " +
            "v8>=:dv81 AND v8<=:dv82 ) OR " +

            "( v0>=:dv01 AND v0<=:dv02 AND " +
            "v1>=:dv11 AND v1<=:dv12 AND " +
            "v2>=:dv21 AND v2<=:dv22 AND " +
            "v3>=:dv31 AND v3<=:dv32 AND " +
            "v9>=:dv91 AND v9<=:dv92 AND " +
            "v5>=:dv51 AND v5<=:dv52 AND " +
            "v6>=:dv61 AND v6<=:dv62 AND " +
            "v7>=:dv71 AND v7<=:dv72 AND " +
            "v8>=:dv81 AND v8<=:dv82 ) OR " +

            "( v0>=:dv01 AND v0<=:dv02 AND " +
            "v1>=:dv11 AND v1<=:dv12 AND " +
            "v2>=:dv21 AND v2<=:dv22 AND " +
            "v9>=:dv91 AND v9<=:dv92 AND " +
            "v4>=:dv41 AND v4<=:dv42 AND " +
            "v5>=:dv51 AND v5<=:dv52 AND " +
            "v6>=:dv61 AND v6<=:dv62 AND " +
            "v7>=:dv71 AND v7<=:dv72 AND " +
            "v8>=:dv81 AND v8<=:dv82 ) OR " +

            "( v0>=:dv01 AND v0<=:dv02 AND " +
            "v1>=:dv11 AND v1<=:dv12 AND " +
            "v9>=:dv91 AND v9<=:dv92 AND " +
            "v3>=:dv31 AND v3<=:dv32 AND " +
            "v4>=:dv41 AND v4<=:dv42 AND " +
            "v5>=:dv51 AND v5<=:dv52 AND " +
            "v6>=:dv61 AND v6<=:dv62 AND " +
            "v7>=:dv71 AND v7<=:dv72 AND " +
            "v8>=:dv81 AND v8<=:dv82 ) OR "

            "( v0>=:dv01 AND v0<=:dv02 AND " +
            "v9>=:dv91 AND v9<=:dv92 AND " +
            "v2>=:dv21 AND v2<=:dv22 AND " +
            "v3>=:dv31 AND v3<=:dv32 AND " +
            "v4>=:dv41 AND v4<=:dv42 AND " +
            "v5>=:dv51 AND v5<=:dv52 AND " +
            "v6>=:dv61 AND v6<=:dv62 AND " +
            "v7>=:dv71 AND v7<=:dv72 AND " +
            "v8>=:dv81 AND v8<=:dv82 ) OR "

            "( v9>=:dv91 AND v9<=:dv92 AND " +
            "v1>=:dv11 AND v1<=:dv12 AND " +
            "v2>=:dv21 AND v2<=:dv22 AND " +
            "v3>=:dv31 AND v3<=:dv32 AND " +
            "v4>=:dv41 AND v4<=:dv42 AND " +
            "v5>=:dv51 AND v5<=:dv52 AND " +
            "v6>=:dv61 AND v6<=:dv62 AND " +
            "v7>=:dv71 AND v7<=:dv72 AND " +
            "v8>=:dv81 AND v8<=:dv82 ) "

            ")"
            , compare
        )
        fetched.extend( cur.fetchall() )

        """
        cur.execute(
            "SELECT image_id "+
            "FROM image_vector10 WHERE "+
            "image_id != :id AND " +
            "v0>=:dv01 AND v0<=:dv02 AND " +
            "v1>=:dv11 AND v1<=:dv12 AND " +
            "v2>=:dv21 AND v2<=:dv22 AND " +
            "v3>=:dv31 AND v3<=:dv32 AND " +
            "v4>=:dv41 AND v4<=:dv42 AND " +
            "v5>=:dv51 AND v5<=:dv52 AND " +
            "v6>=:dv61 AND v6<=:dv62 AND " +
            "v7>=:dv71 AND v7<=:dv72 AND " +
            "v9>=:dv91 AND v8<=:dv92 ",
            compare
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM image_vector10 WHERE "+
            "image_id != :id AND " +
            "v0>=:dv01 AND v0<=:dv02 AND " +
            "v1>=:dv11 AND v1<=:dv12 AND " +
            "v2>=:dv21 AND v2<=:dv22 AND " +
            "v3>=:dv31 AND v3<=:dv32 AND " +
            "v4>=:dv41 AND v4<=:dv42 AND " +
            "v5>=:dv51 AND v5<=:dv52 AND " +
            "v6>=:dv61 AND v6<=:dv62 AND " +
            "v9>=:dv91 AND v9<=:dv92 AND " +
            "v8>=:dv81 AND v8<=:dv82 "
            , compare
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM image_vector10 WHERE "+
            "image_id != :id AND " +
            "v0>=:dv01 AND v0<=:dv02 AND " +
            "v1>=:dv11 AND v1<=:dv12 AND " +
            "v2>=:dv21 AND v2<=:dv22 AND " +
            "v3>=:dv31 AND v3<=:dv32 AND " +
            "v4>=:dv41 AND v4<=:dv42 AND " +
            "v5>=:dv51 AND v5<=:dv52 AND " +
            "v9>=:dv91 AND v9<=:dv92 AND " +
            "v7>=:dv71 AND v7<=:dv72 AND " +
            "v8>=:dv81 AND v8<=:dv82 "
            , compare
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM image_vector10 WHERE "+
            "image_id != :id AND " +
            "v0>=:dv01 AND v0<=:dv02 AND " +
            "v1>=:dv11 AND v1<=:dv12 AND " +
            "v2>=:dv21 AND v2<=:dv22 AND " +
            "v3>=:dv31 AND v3<=:dv32 AND " +
            "v4>=:dv41 AND v4<=:dv42 AND " +
            "v9>=:dv91 AND v9<=:dv92 AND " +
            "v6>=:dv61 AND v6<=:dv62 AND " +
            "v7>=:dv71 AND v7<=:dv72 AND " +
            "v8>=:dv81 AND v8<=:dv82 "
            , compare
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM image_vector10 WHERE "+
            "image_id != :id AND " +
            "v0>=:dv01 AND v0<=:dv02 AND " +
            "v1>=:dv11 AND v1<=:dv12 AND " +
            "v2>=:dv21 AND v2<=:dv22 AND " +
            "v3>=:dv31 AND v3<=:dv32 AND " +
            "v9>=:dv91 AND v9<=:dv92 AND " +
            "v5>=:dv51 AND v5<=:dv52 AND " +
            "v6>=:dv61 AND v6<=:dv62 AND " +
            "v7>=:dv71 AND v7<=:dv72 AND " +
            "v8>=:dv81 AND v8<=:dv82 "
            , compare
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM image_vector10 WHERE "+
            "image_id != :id AND " +
            "v0>=:dv01 AND v0<=:dv02 AND " +
            "v1>=:dv11 AND v1<=:dv12 AND " +
            "v2>=:dv21 AND v2<=:dv22 AND " +
            "v9>=:dv91 AND v9<=:dv92 AND " +
            "v4>=:dv41 AND v4<=:dv42 AND " +
            "v5>=:dv51 AND v5<=:dv52 AND " +
            "v6>=:dv61 AND v6<=:dv62 AND " +
            "v7>=:dv71 AND v7<=:dv72 AND " +
            "v8>=:dv81 AND v8<=:dv82 "
            , compare
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM image_vector10 WHERE "+
            "image_id != :id AND " +
            "v0>=:dv01 AND v0<=:dv02 AND " +
            "v1>=:dv11 AND v1<=:dv12 AND " +
            "v9>=:dv91 AND v9<=:dv92 AND " +
            "v3>=:dv31 AND v3<=:dv32 AND " +
            "v4>=:dv41 AND v4<=:dv42 AND " +
            "v5>=:dv51 AND v5<=:dv52 AND " +
            "v6>=:dv61 AND v6<=:dv62 AND " +
            "v7>=:dv71 AND v7<=:dv72 AND " +
            "v8>=:dv81 AND v8<=:dv82 "
            , compare
        )
        fetched.extend( cur.fetchall() )

        cur.execute(
            "SELECT image_id "+
            "FROM image_vector10 WHERE "+
            "image_id != :id AND " +
            "v0>=:dv01 AND v0<=:dv02 AND " +
            "v9>=:dv91 AND v9<=:dv92 AND " +
            "v2>=:dv21 AND v2<=:dv22 AND " +
            "v3>=:dv31 AND v3<=:dv32 AND " +
            "v4>=:dv41 AND v4<=:dv42 AND " +
            "v5>=:dv51 AND v5<=:dv52 AND " +
            "v6>=:dv61 AND v6<=:dv62 AND " +
            "v7>=:dv71 AND v7<=:dv72 AND " +
            "v8>=:dv81 AND v8<=:dv82 "
            , compare
        )
        fetched.extend( cur.fetchall() )
        """

        cur.execute(
            "SELECT image_id "+
            "FROM image_vector10 WHERE "+
            "image_id != :id AND " +
            "v9>=:dv91 AND v9<=:dv92 AND " +
            "v1>=:dv11 AND v1<=:dv12 AND " +
            "v2>=:dv21 AND v2<=:dv22 AND " +
            "v3>=:dv31 AND v3<=:dv32 AND " +
            "v4>=:dv41 AND v4<=:dv42 AND " +
            "v5>=:dv51 AND v5<=:dv52 AND " +
            "v6>=:dv61 AND v6<=:dv62 AND " +
            "v7>=:dv71 AND v7<=:dv72 AND " +
            "v8>=:dv81 AND v8<=:dv82 "
            , compare
        )
        fetched.extend( cur.fetchall() )

        cur.close()

        items = {}
        for item in fetched:
            id = str(item["image_id"])
            items[ id ] = item

        return tuple(items.values())


    pass
